USE scm_main;
-- 需要先创建拆分字符串存储过程（string_split）
if exists (select 1
          from sysobjects
          where  id = object_id('bd_door_access_remove')
          and type in ('P','PC'))
DROP PROCEDURE bd_door_access_remove
GO
CREATE PROCEDURE bd_door_access_remove
	@xh_str varchar(max),
	@ip varchar(max),
	@operator varchar(max)
as
BEGIN

-- 关闭打印受影响行数
SET NOCOUNT ON;



-- 定义返回受影响行数结果
	declare @result int = 0;


	--  异常扑捉机制
		BEGIN TRY  

		-- 开启事务
			BEGIN TRAN

		-- 1.将数据拆分放进各自的临时表
			select * into #xh_temp from string_split(@xh_str,',');

		-- 2.将需要撤销权限的数据放入临时表中
			select a.xh,a.gate_bh as door_bh,d.door_name,a.user_serial,b.user_no,b.user_lname as user_name,b.user_card,e.dev_serial,e.dev_order,a.rule_no,e.fx
			into #door_access_temp
			from mj_user_rule_real a 
			inner join dt_user b on a.user_serial=b.user_serial 
			inner join st_door_real d on a.gate_bh=d.bh 
			inner join st_doorfx_real e on e.gate_bh=d.bh and e.fx=a.fx 
			inner join #xh_temp t on t.items = a.xh
	
			declare 
				@sum int = 0,
				@index int = 0,
				@xh varchar(200),
				@door_bh varchar(200),
				@door_name varchar(200),
				@user_serial varchar(200),
				@user_no varchar(200),
				@user_name varchar(200),
				@user_card varchar(200),
				@dev_serial varchar(200),
				@dev_order varchar(200),
				@rule_no varchar(200),
				@fx varchar(200)

			select @sum = count(0) from #door_access_temp
	
			WHILE(@index < @sum) BEGIN
		
				select @xh = isnull(xh,''), @door_bh = isnull(door_bh,''), @door_name = isnull(door_name,''), @user_serial = isnull(user_serial,''), @user_no = isnull(user_no,''), @user_card = isnull(user_card,''), @user_name = isnull(user_name,''), @dev_serial = isnull(dev_serial,''), @dev_order = isnull(dev_order,''),@rule_no = isnull(rule_no,''), @fx = isnull(fx,'') 
				from #door_access_temp order by door_bh offset @index rows fetch next 1 rows only

				delete from mj_user_rule_real where xh = @xh

				-- 撤销出 跟踪的SQL 门与部门连起来了 不太正常

				--select rule_no 
				--from MJ_GATE_LEVEL_REAL 
				--where Gate_bh = @door_bh and dep_serial =(select user_dep from dt_user where user_serial='20024642')
		
				-- 201696
				-- (select * from dt_user where user_serial='20024690')
				-- select * from dt_ac_Dep where dep_serial = '201696'

				-- 通知设备拉取数据
				insert into jreal_update_1(jdev_bh,jdata_str)
				select @dev_serial, '101,1,0,'+ @user_card +','+''+','+ @dev_order +','+ @fx +','+isnull(isnull(left(CONVERT(varchar(100), a.pact_begin, 20),10),'2000-01-01')+' 00:00:00,' + left(CONVERT(varchar(100), a.pact_end, 20),10)+' 23:59:59','')
				from dt_user a where a.user_serial = @user_serial


				-- 撤销权限日志
				insert into wt_mj_log_real(lx,log_type,log_state,log_bz,log_sj,log_ip,gly_no,regserial) 
				VALUES (2,3,0,CONCAT('为【',@door_name,':',@door_bh,'】撤销权限,人员:【',@user_name,':',@user_serial,'】,卡号:【',@user_card,'】'),GETDATE(),@ip,@operator,'')


				SET @index = @index + 1;
				SET @result = @index;
			END

		--  提交事务	
			COMMIT TRAN

	--  结束异常捕捉
		END TRY

	--  异常处理
		BEGIN CATCH
			ROLLBACK TRAN;
		END CATCH



--  清除临时表
	select @result as result
	drop table #xh_temp;
	drop table #door_access_temp;


-- 打开打印受影响行数
SET NOCOUNT OFF;
END;
